import sqlite3
import json

def init_db():
    conn = sqlite3.connect('friendship_test.db')
    c = conn.cursor()
    
    # 删除现有表
    c.execute("DROP TABLE IF EXISTS questions")
    c.execute("DROP TABLE IF EXISTS results")
    
    # 创建问题表 - 修改结构以支持填空题
    c.execute('''
        CREATE TABLE IF NOT EXISTS questions (
            id INTEGER PRIMARY KEY,
            question TEXT NOT NULL,
            answer TEXT NOT NULL,
            type TEXT DEFAULT 'blank'  -- 'blank' 表示填空题
        )
    ''')
    
    # 创建结果表
    c.execute('''
        CREATE TABLE IF NOT EXISTS results (
            id INTEGER PRIMARY KEY,
            score INTEGER NOT NULL,
            answers TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 插入示例填空题
    sample_questions = [
        ("李响喜欢下去（4个字）？", "溜达溜达"),
        ("韩豪忌讳的数字?", "1314"),
        ("强哥说的语速很快的六个字是什么?", "为什么不等我"),
        ("fly喜欢治颗?", "白将"),
        ("苏华在什么季节去送的花？", "春季"),
        ("阿刚大一喜欢的人姓什么", "刘")
    ]
    
    c.executemany(
        "INSERT INTO questions (question, answer) VALUES (?, ?)",
        sample_questions
    )
    
    conn.commit()
    conn.close()

def get_questions():
    conn = sqlite3.connect('friendship_test.db')
    c = conn.cursor()
    c.execute("SELECT id, question FROM questions")  # 只返回问题，不返回答案
    questions = [{
        'id': row[0],
        'question': row[1]
    } for row in c.fetchall()]
    conn.close()
    return questions

def check_answers(user_answers):
    conn = sqlite3.connect('friendship_test.db')
    c = conn.cursor()
    
    # 获取所有问题的正确答案
    c.execute("SELECT id, answer FROM questions")
    correct_answers = {str(row[0]): row[1] for row in c.fetchall()}
    
    # 计算得分
    total_questions = len(correct_answers)
    correct_count = 0
    
    for question_id, user_answer in user_answers.items():
        correct_answer = correct_answers.get(question_id, '').lower()
        if user_answer.lower() == correct_answer:
            correct_count += 1
    
    score = int((correct_count / total_questions) * 100)
    conn.close()
    return score

def save_result(score, answers):
    conn = sqlite3.connect('friendship_test.db')
    c = conn.cursor()
    c.execute(
        "INSERT INTO results (score, answers) VALUES (?, ?)",
        (score, json.dumps(answers))
    )
    result_id = c.lastrowid
    conn.commit()
    conn.close()
    return result_id 